﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using Dapper;
using System.Data;

namespace ImportExcel
{
    public class SQLServerHelper
    {
        private readonly string ConnectionString = "";

        public SQLServerHelper()
        {
            var builder = new SqlConnectionStringBuilder();
            builder.DataSource = ".";
            builder.InitialCatalog = "CommodityLabel";
            builder.IntegratedSecurity = true;
            ConnectionString = builder.ConnectionString;
        }

        public SQLServerHelper(string uid, string pwd)
        {
            var builder = new SqlConnectionStringBuilder();
            builder.UserID = uid;
            builder.Password = pwd;
            builder.DataSource = ".";
            builder.InitialCatalog = "CommodityLabel";
            builder.IntegratedSecurity = false;
            ConnectionString = builder.ConnectionString;
        }

        public IEnumerable<T> Query<T>(string sql, object param = null)
        {
            using (var conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                return conn.Query<T>(sql, param);
            }
        }

        public void ExecCmd(string sql, object param = null)
        {
            using (var conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                conn.Execute(sql, param);
            }
        }

        public T QuerySome<T>(string sql, object param = null)
        {
            T result = default(T);
            using (var conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                result = conn.Query<T>(sql, param).FirstOrDefault();
            }
            return result;
        }

        public DataTable GetDataTable(string sql, object param = null)
        {
            DataTable dt = null;
            using (var conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    using (SqlDataAdapter adapter=new SqlDataAdapter(cmd))
                    {
                        var ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds.Tables.Count > 0)
                            dt = ds.Tables[0];
                    }
                }
            }
            return dt;
        }

        public IEnumerable<T> QuerySomes<T>(string sql, object param = null)
        {
            IEnumerable<T> result = default(IEnumerable<T>);
            using (var conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                result = conn.Query<T>(sql, param);
            }
            return result;
        }
    }
}